<?php

namespace App\Console\Commands;
use Maatwebsite\Excel\Facades\Excel;

use Illuminate\Console\Command;
use Illuminate\Support\Facades\DB;
use App\Models\Squad;
use App\Models\Crouse;
use App\Models\Classroom;
use App\Models\Notice;


class analysis extends Command
{
    /**
     * The name and signature of the console command.
     *
     * @var string
     */
    protected $signature = 'analysis';

    /**
     * The console command description.
     *
     * @var string
     */
    protected $description = 'Command description';

    /**
     * Create a new command instance.
     *
     * @return void
     */
    public function __construct()
    {
        parent::__construct();
    }

    /**
     * Execute the console command.
     *
     * @return mixed
     */
    public function handle()
    {
        $stu = DB::table('exam_analysis')->get();

        foreach($stu as $v){
            $update = array();
            $student = DB::table('students as s')->join('squads as sq','sq.id','=','s.squad_id')
                ->where('s.en_name',$v->en_name)->where('sq.name',$v->type.$v->no)->select('s.*')->get();
            $student = json_decode(json_encode($student),true);

            if(!isset($student[0]['squad_id']) || $student[0]['squad_id'] == 0){
                $update['kq_rate'] = 0;
                $update['jx_rate'] = 0;
            }else{
                $kq_num_squad = DB::table('notices')->whereIn('status',['2','4'])->where('notice_type','1')
                    ->where('squad_id',$student[0]['squad_id'])->count();
                $kq_num = DB::table('attendance_records as ar')->join('notices as n','n.id','=','ar.notice_id')
                    ->whereIn('n.status',['2','4'])->where('n.starttime','<','2019-08-31 00:00:00')
                    ->where('ar.attendance_status','1')->where('ar.student_id',$student[0]['id'])->count();
                $update['kq_rate'] = $kq_num_squad ? ($kq_num / $kq_num_squad) : 0;

                $jx_num_squad = DB::table('notices')->whereIn('status',['2','4'])->where('notice_type','3')
                    ->where('squad_id',$student[0]['squad_id'])->count();
                $jx_num = DB::table('jiaxiao_records as jr')->join('notices as n','n.id','=','jr.notice_id')
                    ->whereIn('n.status',['2','4'])
                    ->where('jr.jiaxiao_status','1')->where('jr.student_id',$student[0]['id'])->count();
                $update['jx_rate'] = $jx_num_squad ? ($jx_num / $jx_num_squad) : 0;
            }

            DB::table('exam_analysis')->where('id',$v->id)->update($update);
        }
    }
}
